import requests
import sqlite3
import os
from bs4 import BeautifulSoup

alluniv=[]

def CreateDB():
	hcon=sqlite3.connect('school.db')
	hcur=hcon.cursor()
	stable="""
	create table sc
	(
		id int(10) primary key,
		name varchar(20) not null,
		pac varchar(20),
        fraction varchar(20),
        scale varchar(20)
	)
	"""
	hcur.execute(stable)
	hcur.close()
	hcon.close()
def AddInfo(hcon,hcur,id,name,pac,fraction,scale):
	sql="insert into sc(id,name,pac,fraction,scale) values(?,?,?,?,?)"
	try:
		hcur.execute(sql,(id,name,pac,fraction,scale))
		hcon.commit()
	except:
		hcon.rollback()

def gethtmltext(url):
    try:
        r=requests.get(url,timeout=30)
        r.raise_for_status()
        r.encoding = 'utf-8'
        return r.text
    except:
        return ""

def filllunivlist(soup,hcon,hcur):
    data = soup.find_all('tr')
    for tr in data:
        ltd = tr.find_all('td')
        if len(ltd)==0:
            continue
        singleuniv=[]
        for td in ltd:
            singleuniv.append(td.string)
        alluniv.append(singleuniv)
    for i in range(100):
        u=alluniv[i]
        AddInfo(hcon,hcur,u[0],u[1],u[2],u[3],u[6])
def printunivlist(num):
    print("{:^4}{:^10}{:^5}{:^8}{:^10}".format("排名","学校名称","省市","总分","培养规模"))
    for i in range(num):
        u=alluniv[i]
        print("{:^4}{:^10}{:^5}{:^8}{:^10}".format(u[0],u[1],u[2],u[3],u[6]))
def main(num):
    if os.path.exists('school.db')==False:
        CreateDB()
    hcon=sqlite3.connect('school.db')
    hcur=hcon.cursor()
    url='http://www.zuihaodaxue.cn/zuihaodaxuepaiming2016.html'
    html=gethtmltext(url)
    soup = BeautifulSoup(html,"html.parser")
    filllunivlist(soup,hcon,hcur)
    printunivlist(num)


main(100)